package com.fzrskj.cloud.free.user.dao;

import com.fzrskj.cloud.free.consts.CConsts;
import com.fzrskj.cloud.free.core.enums.DicParamEnum;
import com.fzrskj.cloud.free.core.enums.UserAgentStatusEnum;
import com.fzrskj.cloud.free.core.tool.CDateTimeTool;
import com.fzrskj.cloud.free.core.utils.AgentDataRangeUtils;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.core.utils.DataRangeUtils;
import com.fzrskj.cloud.free.user.bean.UserAccount;
import com.fzrskj.cloud.free.user.bean.UserAccountView;
import com.fzrskj.cloud.free.user.bean.UserAccountViewAgentPro;
import com.winiis.component.common.utils.db.DbUtils;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import ms.core.tool.SysTool;

import java.util.*;

public class UserAccountDao extends CObject{
	//private declarations
	private int UID_NUM = 6600000;
	protected IDbDrv db = null;
	private String SQL_USER_VIEW = "select a.*, d.QQ,d.address,d.certCode,d.certType,d.cityCode,d.companyName,d.email,d.fax,d.fullName,"+
								 "d.isCertified, ifnull(d.certFile, '') as certFile, ifnull(d.certifyDesc, '') as certifyDesc, " +
			   					 "d.industry,d.mainBusiness,d.mobile,d.provinceCode,d.remark,d.tel,d.userType,c.logTime as loginTime,ifnull(f.levelName,'') as levelName,ua.consumeAmount, " +
								" (select paramValue from dic_param dpc where dpc.agentId=a.agentId and dpc.paramName='webname' limit 1) as platName from "+
			   					 "user_account a left join user_accountdata d on a.userId=d.userId "+
			   					 "left join (select max(logTime) as logTime, userName, userId from user_log where module = 'user.login' group by userName limit 1) as c on a.userId=c.userId "+
			   					 "left join dic_vipgrade as f on a.levelCode=f.levelCode and a.agentId=f.agentId "+
								 "left join (select ifnull(sum(amount),0) as consumeAmount,userId from user_accountdetail where itemId=300 group by userId limit 1) as ua on ua.userId=a.userId ";
	private String SQL_USER_VIEW_COUNT = "select userCountDomain,userCountDomainExpire,userCountDomainExpiring from (select count(*) as userCountDomain from user_domain where userName=? and agentId=?) t1,"+
					"(select count(*) as userCountDomainExpire from user_domain where userName = ? and endTime < ? and agentId=?) as t2,"+
					"(select count(*) as userCountDomainExpiring from user_domain where userName = ? and endTime > ? and endTime < ? and agentId=?) as t3";
	private String SQL_SERVICE_VIEW_COUNT = "select userCountService,userCountServiceExpire,userCountServiceExpiring from ("+
	"select count(*) as userCountService from user_service where userId=?) t1,"+
	"(select count(*) as userCountServiceExpire from user_service where userId = ? and endTime < ?) as t2,"+
	"(select count(*) as userCountServiceExpiring from user_service where userId = ? and endTime > ? and endTime < ?) as t3";
	
	private String SQL_USER_ROW ="select count(a.id) from user_account a left join user_accountdata d on a.userId=d.userId and a.agentId=d.agentId ";

	//constructor function to initialize database access object
	public UserAccountDao(){
		db= CloudDbDrvUtils.getDbUtilsDrv();
	}

	//to free resource
	@Override
	public void dispose(){
		db.closeConnection();
	}

	public int updateBlance(int userId,String userName,double balance){
		return this.updateBlance(userId, userName, balance, CloudContextUtils.getAgentId());
	}
	/**
	 * 根据用户名与用户id更新用户余额
	 * @param userName
	 * @param userId
	 * @param balance
	 * @return
	 */
	public int updateBlance(int userId,String userName,double balance, Integer agentId){
		String sql = "update user_account set balance=? where userName=? and userId=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{balance, userName, userId, agentId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 更新用户余额
	 * @param userId
	 * @param userName
	 * @param balance
	 * @return
	 */
	public int updateBlanceWithoutAgent(Integer userId, String userName, Double balance) {
		String sql = "update user_account set balance=? where userName=? and userId=? ";
		int ret = db.executeSql(sql, new Object[]{balance, userName, userId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	public int uptAccountBylevelCode(String userName,int userId,String levelCode){
		return this.uptAccountBylevelCode(userName, userId, levelCode, CloudContextUtils.getAgentId());
	}

	/**
	 * 根据用户名与用户id更新用户余额
	 * @param userName
	 * @param userId
	 * @param levelCode
	 * @return
	 */
	public int uptAccountBylevelCode(String userName,int userId,String levelCode, Integer agentId){

		String sql = "update user_account set levelCode=? where userName=? and userId=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{levelCode, userName, userId, agentId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}
	public UserAccount queryAccountByName(String userName){
		return this.queryAccountByName(userName, CloudContextUtils.getAgentId());
	}
	/**
	 * 根据userName查询对象
	 * @param userName
	 * @return
	 */
	public UserAccount queryAccountByName(String userName, Integer agentId){
		String sql = "select * from user_account where userName=? and agentId=?";
		UserAccount queryAccountByName = db.queryForBean(sql, UserAccount.class, new Object[]{userName, agentId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return queryAccountByName;
	}

	/**
	 * 查询代理商账号信息
	 * @param apiAccessKey user account.
	 * @return agent account info.
	 */
	public UserAccountView queryAgentByAccessKey(String apiAccessKey) {
		String sql = this.SQL_USER_VIEW + " where a.apiAccessKey=? ";
		UserAccountView queryAccountByName = db.queryForBean(sql, UserAccountView.class, new Object[]{apiAccessKey});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return queryAccountByName;
	}
	/**
	 * 根据UserId与UserName删除用户
	 * @param userId user id.
	 * @param userName user name.
	 * @return int.
	 */
	public int deleteUserAccountByUserNameAndUserId(int userId,String userName){
		String sql = "delete from user_account where userId=? and userName=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{userId, userName, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}
	/**
	 * 根据userName与userId修改状态
	 * @param value
	 * @param cdt
	 * @return
	 */
	public int uptUserAccountState(Map<String,Object> value,Map<String,Object> cdt){
		//if (!cdt.containsKey("agentId")){
			cdt.put("agentId", CloudContextUtils.getAgentId());
		//}
		int ret = db.modifyRecord("user_account", value, cdt);
		String code = db.getErrCode();
		this.setErrCode(code);
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}
		return ret;
	}

	public int getNewUserId(){
		String sql = "select max(id) from user_account where userId>=?";
		Object[] params = {UID_NUM};
		Integer uid = (Integer) db.queryFieldValue(sql, Integer.class, params, 1);
		if (uid==null) {
			return UID_NUM;
		}
		return UID_NUM + uid.intValue()+1;
	}
	/**
	 * 根据userName更新code
	 * @param userName
	 * @return
	 */
	public int uptAccountByCode(String userName,String randomNum){
		String sql = "update user_account set randomCode =? where userName=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{randomNum, userName, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		this.setErrCode(code);
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}
		return ret;
	}

	/**
	 * 根据userName更新code
	 * @param
	 * @return
	 */
	public int uptAccountOpenIdByuserId(int userId,String openId,int loginType){
		String sql = "update user_account set openId =?,loginType=? where userId=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{openId, loginType,userId, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		this.setErrCode(code);
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}
		return ret;
	}

	/**
	 * 根据userName与randomCode查找对象
	 * @param userName
	 * @param checkcode
	 * @return
	 */
	public UserAccount queryUserNameAndCode(String userName,String checkcode){
		String sql =" select * from user_account where userName=? and randomCode=? and agentId=?";
		UserAccount queryUserNameAndCode = db.queryForBean(sql, UserAccount.class, new Object[]{userName, checkcode, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		this.setErrCode(code);
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}
		return queryUserNameAndCode;
	}
	//to add new record to database by bean
	public int addUserAccount(UserAccount obj){
		db.startTran();
		//if (CloudContextUtils.getAgentId() > 0) {
			obj.setAgentId(CloudContextUtils.getAgentId());
//		} else {
//			obj.setAgentId(0);
//		}
		if (!UserAgentStatusEnum.IS_AGENT.getValue().equals(obj.getIsAgent())) {
			obj.setIsAgent(UserAgentStatusEnum.NOT_AGENT.getValue());
		}
		obj.setUserId(getNewUserId());
		obj.setIntegral(0);
		obj.setBalance(0);
		if(!"Y".equals(obj.getStatus())) {
			obj.setStatus(CConsts.ACCOUNT_NOT_ACTIVE);        //initial state "N"
		}
		obj.setRandomCode(SysTool.makeUUID(""));
		obj.setRegTime(new Date());
		String sql = "insert into user_account(userId,userName,passWord,integral,balance,"+
					 "levelCode,regTime,regIP,randomCode,status,isAgent,agentId,apiAccessKey,loginType,openId) " +
					"values (?,?,?,?,?," +
					"?,?,?,?,?,?,?,?,?,?);";
		Object[] prm = {obj.getUserId(), obj.getUserName(), obj.getPassword(), obj.getIntegral(), obj.getBalance(),
				obj.getLevelCode(), obj.getRegTime(), obj.getRegIP(), obj.getRandomCode(), obj.getStatus(),
				obj.getIsAgent(), obj.getAgentId(), UUID.randomUUID().toString(),obj.getLoginType(),obj.getOpenId()};
		int ret = db.executeSql(sql,prm);
		String code = db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		if (ret>0) {
			ret = DbUtils.lastId(this.db);
		}
		db.commitTran();
		return ret;
	}

	//to delete table record by id
	public int delUserAccount(int id){

		String sql="delete from user_account where id=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{id, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	//to delete table record by cdt
	public int delUserAccount(Map<String, Object> cdt){
		//if (!cdt.containsKey("agentId")) {
			cdt.put("agentId", CloudContextUtils.getAgentId());
		//}
		int ret = db.deleteRecord("user_account",cdt);
		String code = db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * to modify table record by values and condition
	 * @param values
	 * @param cdt
	 * @return
	 */
	public int modUserAccount(Map<String, Object> values, Map<String, Object> cdt){
		if (!cdt.containsKey("agentId")){
			cdt.put("agentId", CloudContextUtils.getAgentId());
		}
		int ret = db.modifyRecord("user_account", values, cdt);
		String code = db.getErrCode();
		this.setErrCode(code);
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}

		return ret;
	}

	//to modify table record by values and condition
	public boolean activeUserAccount(int rid){
		String sql="update user_account set status='Y' where userId=? and agentId=?";
		int ret = db.executeSql(sql, new Object[]{rid, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return (ret==1);
	}

	//to query user_account item for detail
	public UserAccount queryUserAccount(int uid){
		String sql="select * from user_account where userId=? and agentId=?";
		UserAccount ret = db.queryForBean(sql, UserAccount.class, new Object[]{uid, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	public UserAccount queryUserAccountById(int id){
		String sql="select * from user_account where id=? ";
		UserAccount ret = db.queryForBean(sql, UserAccount.class, new Object[]{id});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	//to query user_account item for detail
	public UserAccount queryUserAccount(String userName){
		String sql="select * from user_account where userName=? and agentId=?";
		UserAccount ret = db.queryForBean(sql, UserAccount.class, new Object[]{userName, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	// to query user_account item for detail
	public UserAccount queryUserAccountEmail(String email) {
		String sql = "select a.* from user_account a left join user_accountdata b on a.userName=b.userName where b.email=? and a.agentId=?";
		UserAccount ret = db.queryForBean(sql, UserAccount.class, new Object[]{email, CloudContextUtils.getAgentId()});
		String code = db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	//to query user_account item for detail
	public UserAccountView queryUserAccountView(int id){
		String sql = SQL_USER_VIEW + " where a.id=? ";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{id});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}
	//to query user_account item for detail
	public UserAccountView queryUserAccountViewDetailed(String userName){
		List<Object> tmp = new ArrayList<>();
		Date date = new Date();
		String dayNow = CDateTimeTool.getDivDay(date, 0);
		String dayNowEnd = CDateTimeTool.getDivDay(date, 7);
		tmp.add(userName);
		tmp.add(CloudContextUtils.getAgentId());
		tmp.add(userName);
		tmp.add(dayNow);
		tmp.add(CloudContextUtils.getAgentId());
		tmp.add(userName);
		tmp.add(dayNow);
		tmp.add(dayNowEnd);
		tmp.add(CloudContextUtils.getAgentId());
		Object[] params = new Object[tmp.size()];
		for (int i=0; i<tmp.size(); i++){
			params[i] = tmp.get(i);
		}
		//todo 不理解SQL,且不是查询会员表,最后整理
		String sqlCout = SQL_USER_VIEW_COUNT;
		UserAccountView domainCount = db.queryForBean(sqlCout, UserAccountView.class, params);
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return domainCount;
	}

	public UserAccountView queryServiceAccountViewDetailed(int uid){
		List<Object> tmp = new ArrayList<>();
		Date date = new Date();
		String dayNow = CDateTimeTool.getDivDay(date, 0);
		String dayNowEnd = CDateTimeTool.getDivDay(date, 7);
		tmp.add(uid);
		tmp.add(uid);
		tmp.add(dayNow);
		tmp.add(uid);
		tmp.add(dayNow);
		tmp.add(dayNowEnd);
		Object[] params = new Object[tmp.size()];
		for (int i=0; i<tmp.size(); i++) {
			params[i] = tmp.get(i);
		}
		//todo 查询user_server表，暂不处理
		String sqlCout = SQL_SERVICE_VIEW_COUNT;
		UserAccountView ServiceCount = db.queryForBean(sqlCout, UserAccountView.class, params);
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ServiceCount;
	}

	/**
	 * 查询虚拟主机产品情况.
	 * @param userId 用户ID.
	 * @return user vhost.
	 */
	public UserAccountView queryUserVhostAccountView(Integer userId) {
		//TODO 查询非用户表,暂不处理
		UserAccountView view = new UserAccountView();
		List<Object> params = new ArrayList<>();
		String sql = "select count(1) as total from user_web where userId=? and isdel=0 ";
		params.add(userId);
		Integer allCount = DbUtils.getCountForMixSql(this.db, new StringBuilder(sql), params);
		Date date = new Date();
		String dayNow = CDateTimeTool.getDivDay(date, 0);
		String dayNowEnd = CDateTimeTool.getDivDay(date, 7);
		String sqlExpire = "select count(1) as total from user_web where userId=? and endTime<?  and isdel=0 ";
		params.add(dayNow);
		Integer allCountExpire = DbUtils.getCountForMixSql(this.db, new StringBuilder(sqlExpire), params);
		String sqlExpiring = "select count(1) as total from user_web where userId=? and endTime>? and endTime<?  and isdel=0 ";
		params.add(dayNowEnd);
		Integer allCountExpiring = DbUtils.getCountForMixSql(this.db, new StringBuilder(sqlExpiring), params);

		view.setUserCountVhost(String.valueOf(allCount));
		view.setUserCountVhostExpire(String.valueOf(allCountExpire));
		view.setUserCountVhostExpiring(String.valueOf(allCountExpiring));
		return view;
	}

	/**
	 * 查询用户自定义服务统计.
	 * @param userId
	 * @return
	 */
	public UserAccountView queryUserDiyAccountView(Integer userId) {
		//TODO 查询非用户表,暂不处理
		UserAccountView view = new UserAccountView();
		List<Object> params = new ArrayList<>();
		String sql = "select count(1) as total from user_diy where userId=? and isdel=0 ";
		params.add(userId);
		Integer allCount = DbUtils.getCountForMixSql(this.db, new StringBuilder(sql), params);
		Date date = new Date();
		String dayNow = CDateTimeTool.getDivDay(date, 0);
		String dayNowEnd = CDateTimeTool.getDivDay(date, 7);
		String sqlExpire = "select count(1) as total from user_diy where userId=? and endTime<?  and isdel=0 ";
		params.add(dayNow);
		Integer allCountExpire = DbUtils.getCountForMixSql(this.db, new StringBuilder(sqlExpire), params);
		String sqlExpiring = "select count(1) as total from user_diy where userId=? and endTime>? and endTime<?  and isdel=0 ";
		params.add(dayNowEnd);
		Integer allCountExpiring = DbUtils.getCountForMixSql(this.db, new StringBuilder(sqlExpiring), params);

		view.setUserCountDiy(String.valueOf(allCount));
		view.setUserCountDiyExpire(String.valueOf(allCountExpire));
		view.setUserCountDiyExpiring(String.valueOf(allCountExpiring));
		return view;
	}

	//to query UserAccount Detail by email
	public UserAccountView queryUserAccountView(String email){
		String sql = SQL_USER_VIEW + " where d.email=? and a.agentId=?";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{email, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 手机账号登录.
	 * @param mobile 手机账号.
	 * @return
	 */
	public UserAccountView queryUserAccountViewByMobile(String mobile) {
		String sql = SQL_USER_VIEW+ " where d.mobile=? and a.agentId=?";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{mobile, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 查询手机号除指定用户外是否被注册使用.
	 * @param mobile 手机号.
	 * @param ignoreUserId 需要忽略的用户ID.
	 * @return isRegisterMobile.
	 */
	public UserAccountView isRegisterMobile(String mobile, Integer ignoreUserId) {
		String sql = SQL_USER_VIEW+ " where d.mobile=? and d.userId<>? and a.agentId=?";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{mobile, ignoreUserId, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 查询邮箱除指定用户外是否被注册使用.
	 * @param mail 邮箱账号.
	 * @param ignoreUserId 需要忽略的用户ID.
	 * @return UserAccountView.
	 */
	public UserAccountView isRegisterMail(String mail, Integer ignoreUserId) {
		String sql = SQL_USER_VIEW+ " where d.email=? and d.userId<>? and a.agentId=?";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{mail, ignoreUserId, CloudContextUtils.getAgentId()});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	public UserAccountView qryUserAccountViewByOpenId(String openId){
//		String sql = SQL_USER_VIEW+ " where d.openId=?  and a.agentId=?";
//		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{openId, CloudContextUtils.getAgentId()});
		String sql = SQL_USER_VIEW+ " where a.openId=? ";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{openId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}


	//to query UserAccount Detail by user id
	public UserAccountView queryUserAccountViewByUid(int uid){
		return this.queryUserAccountViewByUidAppointAgent(uid, CloudContextUtils.getAgentId());
	}

	public UserAccountView queryUserAccountViewByUidAppointAgent(int uid, Integer agentId){
		String sql = SQL_USER_VIEW+ " where a.userId=? and a.agentId=?";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{uid, agentId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 获取用户信息.
	 * @param userId
	 * @return
	 */
	public UserAccountView queryUserAccountWithoutAgent(Integer userId) {
		String sql = SQL_USER_VIEW+ " where a.userId=? ";
		UserAccountView ret = db.queryForBean(sql, UserAccountView.class, new Object[]{userId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	public int queryUserAccountAdmin(Map<String, Object> cdt){
		return this.queryUserAccount(cdt,true);
	}

	public int queryUserAccount(Map<String, Object> cdt){
		return this.queryUserAccount(cdt,false);
	}

	//to query UserAccount record count
	public int queryUserAccount(Map<String, Object> cdt,Boolean isAmind){
		if (!isAmind) {
			cdt.put("a.agentId", CloudContextUtils.getAgentId());
		}
		Object[] params = new Object[cdt.size()];
		int i = 0;
		String sql = SQL_USER_ROW + " where 1=1";
		for (Map.Entry<String, Object> c : cdt.entrySet()) {
			if("a.userName".equals(c.getKey())){
				sql = sql + " and " + c.getKey() + " like ?";
				params[i] = "%"+c.getValue()+"%";
			}else{
				sql = sql + " and " + c.getKey() + "=?";
				params[i] = c.getValue();
			}
			i++;
		}

		//管理角色数据隔离
		sql = DataRangeUtils.formatRangeSql(sql, " a.userId ");

		//代理数据隔离
		sql = AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql, "a.agentId");

		Long ret = (Long) db.queryFieldValue(sql, Long.class, params, 1);
		this.setErrCode(db.getErrCode());
		if (ret==null) {
			return -1;
		}
		return ret.intValue();
	}

	public List<UserAccountView> qryUserAccountListAdmin(Map<String, Object> cdt, int page, int pagesize){
		return this.queryUserAccountList(cdt,page,pagesize,true);
	}

	public List<UserAccountView> queryUserAccountList(Map<String, Object> cdt, int page, int pagesize){
		return this.queryUserAccountList(cdt,page,pagesize,false);
	}

	//to query UserAccount for record list with condition,if page or pagesize less than 0, means without page
	private List<UserAccountView> queryUserAccountList(Map<String, Object> cdt, int page, int pagesize,boolean isAdmin){
		//if (!cdt.containsKey("agentId")) {
		//}
		if(!isAdmin){
			cdt.put("a.agentId", CloudContextUtils.getAgentId());
		}
		Object[] params = new Object[cdt.size()];
		int i = 0;
		String sql = SQL_USER_VIEW + " where 1=1";
		for (Map.Entry<String, Object> c : cdt.entrySet()) {
			if("a.userName".equals(c.getKey())){
				sql = sql + " and " + c.getKey() + " like ?";
				params[i] = "%"+c.getValue()+"%";
			}else{
				sql = sql + " and " + c.getKey() + "=?";
				params[i] = c.getValue();
			}
			i++;
		}
		//管理角色数据隔离
		sql = DataRangeUtils.formatRangeSql(sql, " a.userId ");

		//代理数据隔离
		sql = AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql, " a.agentId ");

		sql+=" order by regtime desc ";
		if (page>0 && pagesize>0) {
			sql += String.format(" limit %d,%d",(page-1)*pagesize,pagesize);
		}

		List<UserAccountView> rets = db.queryForBeans(sql, UserAccountView.class, params);
		this.setErrCode(db.getErrCode());
		return rets;
	}

	// to query UserAccount record count
	public int getUserAccount(Map<String, Object> cdt) {
		List<Object> list = new ArrayList<>();
		StringBuilder sql = new StringBuilder(SQL_USER_ROW + " where 1=1");
		for (Map.Entry<String, Object> c : cdt.entrySet()) {
			if (c.getKey().equals("start")) {
				sql.append(" and a.regTime >=?");
				list.add(c.getValue());
			} else if (c.getKey().equals("end")) {
				sql.append(" and a.regTime <?");
				list.add(c.getValue());
			} else if (c.getKey().equals("agentId")) {
				sql.append(" and a.agentId =?");
				list.add(c.getValue());
			} else {
				sql.append(" and " + c.getKey() + "=?");
				list.add(c.getValue());
			}
		}
		//代理数据隔离
		AgentDataRangeUtils.formatDataRange(CloudContextUtils.getAgentId(), sql, "a.agentId");

		Object[] params = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			params[i] = list.get(i);
		}
		Long ret = (Long) db.queryFieldValue(sql.toString(), Long.class, params, 1);
		this.setErrCode(db.getErrCode());
		if (ret == null || ret < 0) {
			return 0;
		}
		return ret.intValue();
	}

	//更新余额(存储过程)
	public double updateBlance(Object[] params, String[] outname){
		List<Object> outs=DbUtils.executeProc(this.db, "proc_account_balance", params,outname);
		this.setErrCode(db.getErrCode());
		if(outs.size()<=0) {
			return 0;
		}

		double blance = Double.parseDouble(outs.get(0).toString());
		return blance;
	}

	/**
	 * 新增user account记录.
	 * @param values
	 * @return
	 */
	public int newUserAccount(Map<String, Object> values){
		//if (!values.containsKey("agentId")) {
			values.put("agentId", CloudContextUtils.getAgentId());
		//}
		if (!values.containsKey("isAgent")) {
			values.put("isAgent", UserAgentStatusEnum.NOT_AGENT.getValue());
		}
		if (!values.containsKey("apiAccessKey")) {
			values.put("apiAccessKey", UUID.randomUUID().toString());
		}
		int ret = db.insertRecord("user_account", values);
		String err = db.getErrCode();
		if (!err.equals(SysErr.CODE_SUCCESS)) {
			this.log(db.getMsg());
		}

		this.setErrCode(err);
		return ret;
	}

	/**
	 * 根据用户ID获取用户信息（不区分代理商）.
	 * @param userId 用户ID.
	 * @return 用户信息.
	 */
	public UserAccount getUserAccountWithoutAgent(Integer userId) {
		String sql = "select * from user_account where userId=? ";
		UserAccount ret = db.queryForBean(sql, UserAccount.class, new Object[]{userId});
		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return ret;
	}

	/**
	 * 获取所有代理商
	 * @return
	 */
	public List<UserAccountViewAgentPro> getAllSyncProAgentAccount() {
		String sql = "select a.*, (select dp.paramValue from dic_param dp where dp.agentId=a.userId " +
				" and dp.paramName=? and dp.paramType=1 limit 1) as diyPremium  from user_account a where " +
				" a.isAgent='Y' and a.status='Y' and " +
				" ( ISNULL((SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=a.userId)) " +
				" or (SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=a.userId) = 'Y' ) ";
		List<Object> params = new ArrayList<>();
		params.add(DicParamEnum.DIY_PRICE_PREMIUM.getVal());
		params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
		params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
		List<UserAccountViewAgentPro> listAccount = db.queryForBeans(sql, UserAccountViewAgentPro.class, params);

		String code=db.getErrCode();
		if (!code.equals(SysErr.CODE_SUCCESS)){
			this.log(db.getMsg());
		}
		this.setErrCode(code);
		return listAccount;
	}
}